package test.excel;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.util.StringUtils;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class ReadExcel2 {

    public static void read(String filePath) throws Exception{
        read(filePath,0,0);
    }


    public static void read(String filePath,int startRow) throws Exception{
        read(filePath,startRow,0);
    }

    public static void read(String filePath,int startRow,int sheetNum) throws Exception{

        File file = new File(filePath);

        String fileName = file.getName();
        boolean is2003 = true;    //判断是否是excel2007格式
        if (fileName.endsWith("xlsx")) {
            is2003 = false;
        }
        InputStream ins = null;
        try {
            ins = new FileInputStream(file);
            Workbook wb = null;
            //根据文件格式(2003或者2007)来初始化
            if (is2003) {
                wb = new HSSFWorkbook(ins);
            } else {
                wb = new XSSFWorkbook(ins);
            }
            Sheet sheet = wb.getSheetAt(sheetNum);

            StringBuffer sql = new StringBuffer("SELECT a.id,a.shop_name,a.pc_html_status,b.tenant_name FROM tc_shop_info a JOIN tc_tenant_info b ON a. tenant_info_id  = b.id WHERE ") ;

            String merchant_id = "" ;

            for (int i = startRow ; i <= sheet.getLastRowNum(); i++) {

                Row row = sheet.getRow(i);

                merchant_id += getCellValue(row.getCell(2)) + ",";

            }

            merchant_id = merchant_id.substring(0,merchant_id.length()-1);

            sql.append(" a.id IN (" + merchant_id + ")");

            System.out.println(sql.toString());

        } catch (IOException e) {
            throw new Exception(e.getMessage());
        } finally {
            ins.close();
        }
    }

    private static String getCellValue(Cell cell) {
        String cellValue = "";
        // 以下是判断数据的类型
        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_NUMERIC: // 数字
                if (DateUtil.isCellDateFormatted(cell)) {
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                    cellValue = sdf.format(DateUtil.getJavaDate(cell.getNumericCellValue())).toString();
                } else {
                    DataFormatter dataFormatter = new DataFormatter();
                    cellValue = dataFormatter.formatCellValue(cell);
                }
                break;
            case Cell.CELL_TYPE_STRING: // 字符串
                cellValue = cell.getStringCellValue();
                break;
            case Cell.CELL_TYPE_BOOLEAN: // Boolean
                cellValue = cell.getBooleanCellValue() + "";
                break;
            case Cell.CELL_TYPE_FORMULA: // 公式
                cellValue = cell.getCellFormula() + "";
                break;
            case Cell.CELL_TYPE_BLANK: // 空值
                cellValue = "";
                break;
            case Cell.CELL_TYPE_ERROR: // 故障
                cellValue = "非法字符";
                break;
            default:
                cellValue = "未知类型";
                break;
        }
        return cellValue;
    }

    private static boolean isHaveChinese(String str) {
        boolean flag = true;
        if (StringUtils.isEmpty(str)) {
            return flag;
        }
        int count = 0;
        String regEx = "[\\u4e00-\\u9fa5]";
        Pattern p = Pattern.compile(regEx);
        Matcher m = p.matcher(str);

        while (m.find()) {
            for (int i = 0; i <= m.groupCount(); i++) {
                count = count + 1;
            }
        }
        if (count == 0) {
            flag = false;
        }
        return flag;
    }



    public static void main(String[] args) {

        try {
            ReadExcel2.read("/Users/zhangrui/downloads/h5.xlsx",1,0);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}
